using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using System.Data;
using Fieldbook.Entities;
using Fieldbook.Common;

namespace Fieldbook.DAL
{
    public static class FieldWorkCategoryDAO
    {
        #region Insert
        private static DbCommand GetInsertCommand(SqlDatabase db)
        {
            string insertSQL = "INSERT INTO dbo.FieldWorkCategory (DESCRIPTION, HOUSEHOLDID) VALUES (@DESCRIPTION, @HOUSEHOLDID) SET @CATEGORYID = SCOPE_IDENTITY()";
            DbCommand insertCommand = db.GetSqlStringCommand(insertSQL);
            db.AddOutParameter(insertCommand, "@CATEGORYID", DbType.Int64, 8);
            db.AddInParameter(insertCommand, "@DESCRIPTION", DbType.String);
            db.AddInParameter(insertCommand, "@HOUSEHOLDID", DbType.Int64);
            return insertCommand;
        }
        private static void SetInsertValues(SqlDatabase db, DbCommand cmd, FieldWorkCategory entity)
        {
            db.SetParameterValue(cmd, "@DESCRIPTION", entity.Description);
            db.SetParameterValue(cmd, "@HOUSEHOLDID", entity.HouseHoldID);
        }
        public static bool InsertFieldWorkCategory(FieldWorkCategory entity)
        {
            bool res = false;
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetConfigValue(Constants.FieldbookConnectionString));
            DbCommand cmd = GetInsertCommand(db);
            SetInsertValues(db, cmd, entity);
            res = Convert.ToBoolean(db.ExecuteNonQuery(cmd));
            if (res)
            {
                entity.CategoryID = (long?)db.GetParameterValue(cmd, "@CATEGORYID");
            }
            return res;
        }
        public static bool InsertListFieldWorkCategory(List<FieldWorkCategory> list)
        {
            bool res = true;
            DbTransaction transactionObj = null;
            DbConnection conn = null;
            try
            {
                SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetConfigValue(Constants.FieldbookConnectionString));
                DbCommand cmd = GetInsertCommand(db);
                conn = db.CreateConnection();
                conn.Open();
                transactionObj = conn.BeginTransaction();
                for (int i = 0; i < list.Count; i++)
                {
                    FieldWorkCategory entity = list[i];
                    SetInsertValues(db, cmd, entity);
                    res = res & Convert.ToBoolean(db.ExecuteNonQuery(cmd, transactionObj));
                    if (res)
                    {
                        entity.CategoryID = (long?)db.GetParameterValue(cmd, "@CATEGORYID");
                    }
                }
                transactionObj.Commit();
                transactionObj.Dispose();
                conn.Close();
                conn.Dispose();
            }
            catch (Exception ex)
            {
                res = false;
                if (transactionObj != null)
                {
                    transactionObj.Rollback();
                    transactionObj.Dispose();
                }
                if (conn != null & conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                    conn.Dispose();
                }
                throw ex;
            }
            return res;
        }
        #endregion

        #region Delete
        private static DbCommand GetDeleteCommand(SqlDatabase db)
        {
            string deleteSQL = "DELETE FROM dbo.FieldWorkCategory WHERE CATEGORYID = @CATEGORYID";
            DbCommand deleteCommand = db.GetSqlStringCommand(deleteSQL);
            db.AddInParameter(deleteCommand, "@CATEGORYID", DbType.Int64);
            return deleteCommand;
        }
        private static void SetDeleteValues(SqlDatabase db, DbCommand cmd, FieldWorkCategory entity)
        {
            db.SetParameterValue(cmd, "@CATEGORYID", entity.CategoryID);
        }
        public static bool DeleteFieldWorkCategory(FieldWorkCategory entity)
        {
            bool res = false;
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetConfigValue(Constants.FieldbookConnectionString));
            DbCommand cmd = GetDeleteCommand(db);
            SetDeleteValues(db, cmd, entity);
            res = Convert.ToBoolean(db.ExecuteNonQuery(cmd));
            return res;
        }
        public static bool DeleteFieldWorkCategory(long categoryID)
        {
            bool res = false;
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetConfigValue(Constants.FieldbookConnectionString));
            DbCommand cmd = GetDeleteCommand(db);
            db.SetParameterValue(cmd, "@CATEGORYID", categoryID);
            res = Convert.ToBoolean(db.ExecuteNonQuery(cmd));
            return res;
        }
        #endregion
    }
}
